package ru.zombator.taskman.core.dbclient.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import ru.zombator.taskman.core.dbclient.DbException;
import ru.zombator.taskman.core.dbclient.IDbClient;
import ru.zombator.taskman.core.dbclient.TaskCriteria;
import ru.zombator.taskman.core.domain.Datestamp;
import ru.zombator.taskman.core.domain.IManager;
import ru.zombator.taskman.core.domain.IPerson;
import ru.zombator.taskman.core.domain.ITask;
import ru.zombator.taskman.core.domain.ITaskProject;
import ru.zombator.taskman.core.domain.IWorkTime;
import ru.zombator.taskman.core.domain.IWorker;
import ru.zombator.taskman.core.utils.CryptoUtils;

/**
 * Клиент, обращающийся к локальной БД SQL Server, находящейся
 * на той же машине.
 * 
 * @author nvamelichev
 *
 */
public class LocalDbClient implements IDbClient {
	/**
	 * строка для соединения с SQL Server
	 */
	private static final String CONNECTION_STRING = 
		"jdbc:sqlserver://localhost:1433;databaseName=plan;integratedSecurity=true";
	
	/**
	 * создает соединение
	 * @return	соединение
	 * @throws SQLException - не удалось связаться с базой
	 */
	private Connection createConnection() throws SQLException {
		return DriverManager.getConnection(CONNECTION_STRING);
	}
	
	@Override
	public IPerson addUser(String name, String ipAddress, Role role)
			throws DbException {
		Connection con = null;
		CallableStatement st = null;		
		try {
			con = createConnection();
			st = con.prepareCall("{call AddUser(?, ?, ?, ?)}");
			st.registerOutParameter(4, Types.CHAR);
			st.setString("Name", name);
			st.setString("IpAddress", ipAddress);
			st.setInt("IsManager", role.ordinal());
			st.execute();
			String guidString = st.getString("UserId");
			UUID id = UUID.fromString(guidString);
			
			return (role.equals(Role.MANAGER) ? new Manager(id, name, ipAddress) :
				new Worker(id, name, ipAddress));
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public void assignWorkerToTask(IManager manager, ITask task, IWorker worker)
			throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call AssignTaskTo(?,?,?)}");
			st.setString("ManagerId", manager.getId().toString());
			st.setString("WorkerId", worker.getId().toString());
			st.setString("TaskId", task.getId().toString());
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public void changeUser(IPerson user) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call AlterUser(?,?,?,?)}");
			st.setString("UserId", user.getId().toString());
			st.setString("Name", user.getName());
			st.setString("IpAddress", user.getIpAddress());
			st.setInt("IsManager", user instanceof IManager ? 1 : 0);
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public ITaskProject createProject(IManager manager, String name,
			String password) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call CreateProject(?,?,?,?)}");
			
			st.setString("ManagerId", manager.getId().toString());
			st.setString("ProjectName", name);
			
			String passwordHash = CryptoUtils.sha1(password);
			st.setString("PasswordHash", passwordHash);
			st.registerOutParameter("ProjectId", Types.CHAR);
			st.execute();
			
			String guidString = st.getString("ProjectId");
			UUID id = UUID.fromString(guidString);						
			
			return new TaskProject(id, name, passwordHash);
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public ITask createTask(IManager manager, ITaskProject project,
			String name, Datestamp startsWhen, Datestamp endsWhen)
			throws DbException {
        Connection con = null;
        CallableStatement st = null;
        try {
            con = createConnection();
            st = con.prepareCall("{call AddTask(?,?,?,?,?,?)}");
            
            st.setString("ManagerId", manager.getId().toString());
            st.setString("ProjectId", project.getId().toString());
            st.setString("Name", name);
            st.setDate("DateStart", startsWhen.asSqlDate());
            st.setDate("DateEnd", endsWhen.asSqlDate());
            st.registerOutParameter("TaskId", Types.CHAR);
            st.execute();
            
            String guidString = st.getString("TaskId");
            UUID id = UUID.fromString(guidString);                      
            
            return new Task(id, name, startsWhen, endsWhen);
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }	    
	}

	@Override
	public void dropTask(IManager manager, ITask task) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call DropTask(?,?)}");
			st.setString("ManagerId", manager.getId().toString());
			st.setString("TaskId", task.getId().toString());
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();				
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public void dropUser(IPerson user) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call DropUser(?)}");
			st.setString("UserId", user.getId().toString());
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();				
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public List<ITaskProject> getMyProjects(IPerson user) throws DbException {
        List<ITaskProject> projects = new ArrayList<ITaskProject>();
        Connection con = null;
        Statement st = null;
        try {
            con = createConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("select id, name from " +
                    "Projects inner join UserToProject on Projects.id=UserToProject.project_id " +
                    "where UserToProject.user_id='" + user.getId().toString() + "'");
            
            while (rs.next()) {
                UUID id = UUID.fromString(rs.getString("id"));
                String name = rs.getString("name");

                ITaskProject project = new TaskProject(id, name, null);
                projects.add(project);
            }
            
            return projects;
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }	    
	}

	@Override
	public List<ITaskProject> getProjects() throws DbException {
        List<ITaskProject> projects = new ArrayList<ITaskProject>();
        Connection con = null;
        Statement st = null;
        try {
            con = createConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from Projects");
            
            while (rs.next()) {
                UUID id = UUID.fromString(rs.getString("id"));
                String name = rs.getString("name");

                ITaskProject project = new TaskProject(id, name, null);
                projects.add(project);
            }
            
            return projects;
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }
	}

	@Override
	public List<ITask> getTasks(TaskCriteria criteria) throws DbException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public IPerson getUser(String ipAddress) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		Statement st2 = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call GetUser(?,?)}");
			st.setString("IpAddress", ipAddress);
			st.registerOutParameter("UserId", Types.CHAR);
			st.execute();
			
			String uuidAsStr = st.getString("UserId");
			if (uuidAsStr == null)
				return null;
			UUID id = UUID.fromString(uuidAsStr);
			
			st2 = con.createStatement();
			ResultSet rs = st2.executeQuery("select * from Users where id='" + uuidAsStr + "'");
			rs.next();
			String name = rs.getString("name");
			int isManager = rs.getInt("is_manager");
			
			return (isManager == 1 ? new Manager(id, name, ipAddress) :
				new Worker(id, name, ipAddress));
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();				
			} catch (SQLException se) {
			}
		}
	}
	
	@Override
	public List<IPerson> getUsers() throws DbException {
		List<IPerson> persons = new ArrayList<IPerson>();
		Connection con = null;
		Statement st = null;
		try {
			con = createConnection();
			st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from Users");
			
			while (rs.next()) {
				UUID id = UUID.fromString(rs.getString("id"));
				String name = rs.getString("name");
				String ipAddress = rs.getString("ip");
				int isManager = rs.getInt("is_manager");
				
				IPerson person = (isManager == 1 ? new Manager(id, name, ipAddress) :
					new Worker(id, name, ipAddress));
				persons.add(person);
			}
			
			return persons;
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public void joinProject(IManager manager, ITaskProject project,
			String password) throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call JoinProject(?,?,?)}");
			
			st.setString("ManagerId", manager.getId().toString());
			st.setString("ProjectId", project.getId().toString());
			
			String passwordHash = CryptoUtils.sha1(password);
			st.setString("PasswordHash", passwordHash);
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}		
	}

	@Override
	public void setTaskDescription(IManager manager, ITask task)
			throws DbException {
        Connection con = null;
        CallableStatement st = null;
        try {
            con = createConnection();
            st = con.prepareCall("{call SetTaskDescription(?,?,?)}");
            
            st.setString("ManagerId", manager.getId().toString());
            st.setString("TaskId", task.getId().toString());
            st.setString("Description", task.getDescription());
            
            st.execute();
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }    
	}

	@Override
	public void setTaskStatus(IWorker worker, ITask task) throws DbException {
        Connection con = null;
        CallableStatement st = null;
        try {
            con = createConnection();
            st = con.prepareCall("{call SetTaskState(?,?,?)}");
            
            st.setString("UserId", worker.getId().toString());
            st.setString("TaskId", task.getId().toString());
            st.setInt("NewState", task.getStatus().ordinal());
            
            st.execute();
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }
	}

	@Override
	public void trackTime(IWorker worker, ITask task, Datestamp date, int hours)
			throws DbException {
        Connection con = null;
        CallableStatement st = null;
        try {
            con = createConnection();
            st = con.prepareCall("{call TrackTime(?,?,?,?)}");
            
            st.setString("WorkerId", worker.getId().toString());
            st.setString("TaskId", task.getId().toString());
            st.setDate("When", date.asSqlDate());
            st.setInt("Hours", hours);
            
            st.execute();
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }
	}

	@Override
	public void unAssignWorker(IManager manager, ITask task, IWorker worker)
			throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call UnAssignTaskFrom(?,?,?)}");
			st.setString("ManagerId", manager.getId().toString());
			st.setString("WorkerId", worker.getId().toString());
			st.setString("TaskId", task.getId().toString());
			st.execute();
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}

	@Override
	public List<IWorkTime> getWorkTimes(ITask task) throws DbException {
        List<IWorkTime> times = new ArrayList<IWorkTime>();
        Connection con = null;
        Statement st = null;
        try {
            con = createConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from Times " +
                    "where task_id='" + task.getId().toString() + "'");
            
            while (rs.next()) {
                UUID timeId = UUID.fromString(rs.getString("time_id"));
                UUID userId = UUID.fromString(rs.getString("user_id"));

                Statement st2 = con.createStatement();
                ResultSet rs2 = st2.executeQuery("select * from Workers " +
                        "where id='" + userId.toString() + "'");
                rs2.next();
                String userName = rs2.getString("name");
                String userIp = rs2.getString("ip");
                
                Date when = rs.getDate("date_when");
                byte hours = (byte) rs.getInt("hours");
                
                IWorker who = new Worker(userId, userName, userIp);
                IWorkTime time = new WorkTime(timeId, who, new Datestamp(when),
                        hours);
                times.add(time);
            }
            
            return times;
        } catch (SQLException se) {
            throw new DbException(se.getMessage(), se);
        } finally {
            try {
                if (con != null) con.close();
            } catch (SQLException se) {
            }
        }	    
	}

	@Override
	public List<IWorker> getWorkers(ITask task) throws DbException {
		List<IWorker> workers = new ArrayList<IWorker>();
		
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call GetWorkersForTask(?)}");
			st.setString("TaskId", task.getId().toString());
			st.execute();
			
			ResultSet rs = st.getResultSet();
			while (rs.next()) {
				String idStr = rs.getString("user_id");
				UUID id = UUID.fromString(idStr);
		
				String name = rs.getString("name");
				String ipAddress = rs.getString("ip");
				
				IWorker worker = new Worker(id, name, ipAddress);
				workers.add(worker);
			}
			
			return workers;
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();
			} catch (SQLException se) {
			}
		}
	}
	
	@Override
	public List<ITaskProject> getNonjoinedProjects(IManager manager)
			throws DbException {
		Connection con = null;
		CallableStatement st = null;
		try {
			con = createConnection();
			st = con.prepareCall("{call GetNonJoinedProjects(?)}");
			st.setString("ManagerId", manager.getId().toString());
			
			st.execute();
			ResultSet rs = st.getResultSet();
			
			List<ITaskProject> projects = new ArrayList<ITaskProject>();
			while (rs.next()) {
				UUID id = UUID.fromString(rs.getString("id"));
				String name = rs.getString("name");
				
				ITaskProject project = new TaskProject(id, name, null);
				projects.add(project);
			}
			return projects;
		} catch (SQLException se) {
			throw new DbException(se.getMessage(), se);
		} finally {
			try {
				if (con != null) con.close();				
			} catch (SQLException se) {
			}
		}
	}

}
